package com.example.dao;

import com.example.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface OperationMapper {
    //机票操作
    //退票
    @Select("select * from ftos.ticket where id = #{ticketId}")
    Ticket getTicketById(int ticketId);
    @Update("update ftos.ticket set statusText = '已取消', status = 'cancelled' where id = #{ticketId}")
    int cancelTicket(int ticketId);
    @Select("select passengers from ftos.`order` where id = #{orderId}")
    int getPassengersByOrderId(int orderId);
    @Update("update ftos.`order` set passengers = passengers - 1, price = price - #{price} where `order`.id = #{o_id}")
    int reducePassengerPriceOrder(@Param("orderId") int orderId, @Param("price") double price);
    @Update("update ftos.`order` set status = 'cancelled', statusText = '已取消', cancelDate = #{cancelDate} where id = #{orderId}")
    int cancelOrder(@Param("orderId") int orderId, @Param("cancelDate") String cancelDate);
    @Update("update ftos.`order` set status = 'refund', statusText = '退款中', refundDate = #{refundDate} where id = #{orderId}")
    int refundOrder(@Param("orderId") int orderId, @Param("refundDate") String refundDate);
    @Update("update ftos.flight_status set economy_space = economy_space + 1 where f_id = #{f_id}")
    int addEconomySpaceByFid(int f_id);
    @Update("update ftos.flight_status set business_space = business_space + 1 where f_id = #{f_id}")
    int addBusinessSpaceByFid(int f_id);
    @Update("update ftos.flight_status set first_space = first_space + 1 where f_id = #{f_id}")
    int addFirstSpaceByFid(int f_id);
    @Update("update ftos.passenger set order_num = order_num - 1 where ID_type = #{ID_type} and passenger_ID_card = #{ID_card}")
    int reducePassengerOrderNumByPassengerId(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card);
    @Update("update ftos.ticket set status = 'refunded', statusText = '待退款' where id = #{t_id}")
    int refundTicket(int t_id);
    @Update("update ftos.ticket set order_id = 0 where id = #{t_id}")
    int setOrderIdinTicket(int t_id);
    //再次预定
    @Select("select * from ftos.flight where f_id = #{f_id}")
    Flight getFlightById(int f_id);
    //改签
    @Select("select * from ftos.`order` where id = #{o_id}")
    Order getOrderById(int o_id);
    @Select("select * from ftos.user where u_id = #{u_id}")
    User getUserById(int u_id);
    @Select("select * from ftos.flight_status where f_id = #{f_id}")
    FlightStatus getFlightStatusById(int f_id);
    @Update("update ftos.bank set money = money + #{money} where ID_type = #{ID_type} and u_ID_card = #{ID_card}")
    int refundMoneyByUserIDtypeAndIDcard(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card, @Param("money") double money);
    @Delete("delete from ftos.ticket where id = #{t_id}")
    int deleteTicket(int t_id);
    @Update("update ftos.flight_status set economy_space = economy_space - 1 where f_id = #{f_id}")
    int reduceEconomySpaceByFid(int f_id);
    @Update("update ftos.flight_status set business_space = business_space - 1 where f_id = #{f_id}")
    int reduceBusinessSpaceByFid(int f_id);
    @Update("update ftos.flight_status set first_space = first_space - 1 where f_id = #{f_id}")
    int reduceFirstSpaceByFid(int f_id);
    @Insert("INSERT INTO ftos.`order`(userId, user_name, flightNo, airline, date, `from`,`to`, departure, arrival, price, passengers, status, statusText, paymentMethod, paymentDate) " +
            "VALUES(#{userId}, #{user_name}, #{flightNo}, #{airline}, #{date}, #{from}, #{to}, #{departure}, #{arrival}, #{price}, #{passengers}, #{status}, #{statusText}, #{paymentMethod}, #{paymentDate})")
    int addOrder(@Param("userId") int userId,@Param("user_name") String user_name, @Param("flightNo") int flightNo, @Param("airline") String airline, @Param("date") String date, @Param("from") String from, @Param("to") String to, @Param("departure") String departure, @Param("arrival") String arrival, @Param("price") double price, @Param("passengers") int passengers, @Param("status") String status,@Param("statusText") String statusText, @Param("paymentMethod") String paymentMethod, @Param("paymentDate") String paymentDate);
    @Select("SELECT * FROM ftos.`order` ORDER BY id DESC LIMIT 1")
    int getLastOrderId();
    @Insert("INSERT INTO ftos.ticket(order_id, passenger_id, passenger_name, passenger_type, flightNo, date, price, seat_type, status, statusText) " +
            "VALUES(#{orderId}, #{passengerId}, #{passengerName}, #{passengerType}, #{flightNo}, #{date}, #{price}, #{seatType}, #{status}, #{statusText})")
    int addTicket(@Param("orderId") int orderId, @Param("passengerId") int passengerId, @Param("passengerName") String passengerName, @Param("passengerType") String passengerType, @Param("flightNo") int flightNo, @Param("date") String date, @Param("price") double price, @Param("seatType") String seatType, @Param("status") String status, @Param("statusText") String statusText);
    @Delete("delete from ftos.`order` where id = #{o_id}")
    int deleteOrder(int o_id);
    @Update("update ftos.bank set money = money - #{money} where ID_type = #{ID_type} and u_ID_card = #{ID_card}")
    int reduceMoneyByUserIDtypeAndIDcard(@Param("ID_type") String ID_type, @Param("ID_card") String ID_card, @Param("money") double money);

    //订单操作
    //取消订单
    @Select("select * from ftos.ticket where order_id = #{o_id}")
    List<Ticket> getTicketsByOrderId(int o_id);
    //支付
    @Update("update ftos.ticket set statusText = '计划中',status = 'scheduled' where order_id = #{o_id}")
    int scheduledTicketStatus(int o_id);
    @Update("update ftos.`order` set status = 'paid', statusText = '已支付', paymentDate = #{paymentDate},paymentMethod = #{paymentMethod} where id = #{orderId}")
    int payOrder(@Param("orderId") int orderId,@Param("paymentDate") String paymentDate,@Param("paymentMethod") String paymentMethod);

    //管理员
    //批准退款
    @Update("update ftos.ticket set statusText = '已取消', status = 'cancelled'  where order_id = #{o_id}")
    int cancelTicketByOrderId(int o_id);
    @Update("update ftos.`order` set status = 'paid', statusText = '已支付' where id = #{orderId}")
    int payOrderNoDate(int orderId);
    //延误
    @Update("update ftos.ticket set statusText = '已延误', status = 'delay' where flightNo = #{f_id}")
    int delayTicketByFlightId(int f_id);
    @Update("update ftos.flight_status set status = '已延误' where f_id = #{f_id}")
    int delayFlightStatusByFlightId(int f_id);
    @Select("select * from ftos.ticket where flightNo = #{f_id}")
    List<Ticket> getTicketsByFlightId(int f_id);
    @Select("select email from ftos.user u join ftos.passenger p on u.ID_type = p.ID_type and u.ID_card = p.passenger_ID_card  where passenger_id = #{passenger_id}")
    String getEmailByPassengerId(int passenger_id);
    //删除
    @Delete("delete from ftos.flight where f_id = #{f_id}")
    int deleteFlight(int f_id);
    @Delete("delete from ftos.flight_status where f_id = #{f_id}")
    int deleteFlightStatus(int f_id);
    @Delete("delete from ftos.ticket where flightNo = #{f_id}")
    int deleteTicketByFlightId(int f_id);
    @Delete("delete from ftos.`order` where flightNo = #{f_id}")
    int deleteOrderByFlightId(int f_id);
    @Delete("delete from ftos.plane where plane_id = #{p_id}")
    int deletePlane(int p_id);
    //编辑
    @Select({
            "SELECT f.departure_airport, f.departure_city, f.departure_date, f.departure_day, f.departure_time, " +
            "       f.arrival_city, f.arrival_airport, f.arrival_date, f.arrival_day, f.arrival_time," +
            "       f.distance, f.economy_price, f.business_price, f.first_price," +
            "       f.meal_money, f.meal, f.gate, f.baggage_limit," +
            "       fs.captain, fs.assistant_captain, fs.chief_attendant, fs.flight_crew_num," +
            "       fs.economy_space, fs.business_space, fs.first_space, fs.status," +
            "       p.plane_id, p.p_model, p.p_airline, p.plane_age, p.WiFi, p.capacity," +
            "       p.economy_seat, p.business_seat, p.first_seat" +
            " FROM ftos.flight f" +
            " JOIN ftos.flight_status fs ON f.f_id = fs.f_id" +
            " JOIN ftos.plane p ON f.plane_id = p.plane_id" +
            " WHERE f.f_id = #{f_id}" +
            " LIMIT 1"
    })
    FlightWithStatus getFlightWithStatus(int f_id);
    @Update("UPDATE ftos.flight SET gate = #{gate}, departure_date = #{departure_date}, arrival_date = #{arrival_date}, " +
            "departure_time = #{departure_time}, arrival_time = #{arrival_time}, distance = #{distance}, " +
            "economy_price = #{economy_price}, business_price = #{business_price}, first_price = #{first_price}, " +
            "baggage_limit = #{baggage_limit}, meal_money = #{meal_money}, meal = #{meal} " +
            "where f_id = #{f_id}")
    int editFlight(@Param("f_id") int f_id, @Param("gate") String gate, @Param("departure_date") String departure_date, @Param("arrival_date") String arrival_date, @Param("departure_time") String departure_time, @Param("arrival_time") String arrival_time, @Param("distance") double distance, @Param("economy_price") double economy_price, @Param("business_price") double business_price,@Param("first_price") double first_price, @Param("baggage_limit") int baggage_limit, @Param("meal_money") String meal_money, @Param("meal") String meal);
    @Update("update ftos.flight_status set status = #{status}, captain = #{captain}, assistant_captain = #{assistant_captain}, chief_attendant = #{chief_attendant}, flight_crew_num = #{flight_crew_num} where f_id = #{f_id}")
    int editFlightStatus(@Param("f_id") int f_id, @Param("status") String status, @Param("captain") String captain, @Param("assistant_captain") String assistant_captain, @Param("chief_attendant") String chief_attendant, @Param("flight_crew_num") int flight_crew_num);
    //起飞
    @Update("update ftos.flight_status set status = '已起飞' where f_id = #{f_id}")
    int flightStart(int f_id);
    @Update("update ftos.ticket set status = 'completed',statusText = '已完成' where flightNo = #{f_id}")
    void ticketFlightStart(int f_id);
    @Update("update ftos.`order` set status = 'completed', statusText = '已完成' where flightNo = #{f_id}")
    void orderFlightStart(int f_id);
    //到达
    @Update("update ftos.flight_status set status = '已到达' where f_id = #{f_id}")
    int flightArrive(int f_id);
}
